package com.wolfking.converter.web;

import com.alibaba.excel.EasyExcel;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import com.google.common.io.Files;
import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.reflect.TypeToken;
import com.wolfking.converter.entity.ExecuteResult;
import com.wolfking.converter.entity.SqlDataSource;
import com.wolfking.converter.entity.SqlDefine;
import com.wolfking.converter.entity.SqlDefineParam;
import com.wolfking.converter.event.SqlExportEvent;
import com.wolfking.converter.event.SqlQueryEvent;
import com.wolfking.converter.exception.MoreTransformException;
import com.wolfking.converter.exception.ParamVerifyException;
import com.wolfking.converter.service.JdbcTemplateService;
import com.wolfking.converter.service.SqlConfigInterface;
import com.wolfking.converter.service.SqlConverterExecuteService;
import com.wolfking.jeesite.event.AbstractSignEvent;
import com.wolfking.jeesite.freemarker.TemplateRenderService;
import com.wolfking.jeesite.sign.Sign;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.exception.ExceptionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.MissingServletRequestParameterException;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;

import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.net.URLDecoder;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Slf4j
@Controller
@RequestMapping("/sql")
public class SqlController {

    @Autowired
    @SuppressWarnings("all")
    private SqlConfigInterface sqlConfigInterface;

    @Autowired
    private SqlConverterExecuteService sqlConverterExecuteService;

    @Autowired
    private JdbcTemplateService jdbcTemplateService;

    @Autowired
    private TemplateRenderService templateRenderService;


    private final MediaType mediaType = MediaType.valueOf("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

    @Sign(event = AbstractSignEvent.class)
    @RequestMapping(value = "/detail", method = {RequestMethod.POST, RequestMethod.GET})
    public String detail(Model model, @RequestParam("index") String index, @RequestParam("head") String head, @RequestParam("json") String json) throws Exception {
        model.addAttribute("index", index);
        head = URLDecoder.decode(head, "UTF-8");
        model.addAttribute("head", Arrays.stream(head.split(",")).collect(Collectors.toList()));
        if (StringUtils.isBlank(json)) {
            json = "{}";
        }
        json = new String(Base64.getDecoder().decode(json), StandardCharsets.UTF_8);
        Map<String, String> map = new Gson().fromJson(json, new TypeToken<Map<String, String>>() {
        }.getType());
        model.addAttribute("json", map);
        return "sql/detail";
    }

    @SuppressWarnings("all")
    @Sign(event = SqlExportEvent.class)
    @RequestMapping(value = "/export", method = RequestMethod.GET)
    public ResponseEntity<?> export(HttpServletRequest request, @RequestParam("defineKey") String defineKey) {
        try {
            Map<String, Object> params = getParams(request);
            params.put("key", defineKey);
            List<Map<String, String>> mapList = Lists.newArrayList();
            ExecuteResult executeResult = sqlConverterExecuteService.executeResult(params, true);
            List<String> head = executeResult.getColumnNames();
            assemblyMapList(mapList, executeResult.getArray(), head);
            HttpHeaders headers = new HttpHeaders();
            HttpStatus httpStatus = HttpStatus.OK;
            File file = File.createTempFile(String.format("%s-%s", params.get("key"), System.currentTimeMillis()), "xlsx");
            headers.add("Content-Disposition", String.format("attachment;filename=%s-%s.xlsx", params.get("key"), System.currentTimeMillis()));
            headers.setContentType(mediaType);
            List<List<String>> excelHead = head.stream().map(Lists::newArrayList).collect(Collectors.toList());
            List<List<String>> excelData = mapList.stream().map(one -> head.stream().map(one::get).map(String::valueOf)
                    .collect(Collectors.toList())).collect(Collectors.toList());
            EasyExcel.write(file)
                    .useDefaultStyle(false)
                    .head(excelHead).sheet("模板")
                    .doWrite(excelData);
            return new ResponseEntity<>(Files.toByteArray(file), headers, httpStatus);
        } catch (Exception e) {
            log.error("", e);
            return new ResponseEntity<>(ExceptionUtils.getStackTrace(e).replace("\n", "<BR>"), HttpStatus.BAD_REQUEST);
        }
    }

    @Sign(code = "sql:query", event = SqlQueryEvent.class)
    @RequestMapping(value = "/query", method = {RequestMethod.GET, RequestMethod.POST})
    public String query(Model model, HttpServletRequest request, @RequestParam(value = "sql_type", defaultValue = "change") String sqlType,
                        @RequestParam(value = "defineKey", defaultValue = "") String defineKey,
                        @RequestParam(value = "staticKey", defaultValue = "false") boolean staticKey) {
        String title = "sql查询";
        boolean autoWebLoad = false;
        boolean htmlCenterShow = false;
        List<SqlDefine> sqlDefineList = sqlConfigInterface.querySqlDefineList();
        model.addAttribute("httpMethod", request.getMethod());
        if (request.getMethod().equals("GET") && StringUtils.isNotBlank(defineKey)) {
            staticKey = true;
        }
        if (staticKey) {
            String finalKey = defineKey;
            Optional<SqlDefine> first = sqlDefineList.stream().filter(d -> d.getDefineKey().equals(finalKey)).findFirst();
            if (first.isPresent()) {
                title = first.get().getSqlDescribe();
                if (request.getMethod().equalsIgnoreCase("GET")) {
                    autoWebLoad = first.get().getAutoWebLoad();
                }
                htmlCenterShow = first.get().getHtmlCenterShow();
            } else {
                staticKey = false;
            }
        }
        List<SqlDefineParam> sqlParams = Lists.newArrayList();
        if (StringUtils.isBlank(defineKey) && sqlDefineList.size() > 0) {
            //原始的下拉选择SQL的列表的页面，没有defineKey参数
            defineKey = sqlDefineList.get(0).getDefineKey();
        }
        if (StringUtils.isNotBlank(defineKey)) {
            sqlParams = sqlConfigInterface.querySqlDefineParam(defineKey).stream()
                    .sorted(Comparator.comparingInt(SqlDefineParam::getSort)).collect(Collectors.toList());
        }
        //获取请求参数
        Map<String, Object> params = getParams(request);
        String errorMessage = "";
        List<String> head = Lists.newArrayList();
        List<Object> mapList = Lists.newArrayList();
        Set<String> hideColumns = Sets.newHashSet();
        if ("select".equals(sqlType)) {
            String finalKey = defineKey;
            SqlDefine sqlDefine = sqlDefineList.stream().filter(d -> d.getDefineKey().equalsIgnoreCase(finalKey))
                    .findFirst().orElseGet(() -> sqlConfigInterface.querySqlDefineByKey(finalKey));
            hideColumns = sqlDefine.getHideColumSet();
            try {
                ExecuteResult executeResult = sqlConverterExecuteService.executeResult(sqlDefine, sqlParams, params, false);
                head = executeResult.getColumnNames();
                assemblyMapList(mapList, executeResult.getArray(), head);
                if (executeResult.getPage() != null) {
                    model.addAttribute("page", executeResult.getPage());
                }
            } catch (Exception e) {
                errorMessage = getExceptionMessage(e);
            }
        }
        assemblySqlParam(sqlParams, params, "select".equals(sqlType));
        if (StringUtils.isNotBlank(errorMessage)) {
            model.addAttribute("messageType", "error");
            model.addAttribute("message", errorMessage.replace("\n", "<BR>")
                    .replace(" ", "&nbsp;&nbsp;&nbsp;").replace("\t", "&nbsp;&nbsp;&nbsp;&nbsp;"));
        }
        model.addAttribute("hideColumns", hideColumns);
        model.addAttribute("title", title);
        model.addAttribute("sqls", sqlDefineList);
        model.addAttribute("staticKey", staticKey);
        model.addAttribute("sqlParams", sqlParams);
        model.addAttribute("defineKey", defineKey);
        model.addAttribute("thead", head);
        model.addAttribute("theadSplit", String.join(",", head));
        model.addAttribute("tbody", mapList);
        model.addAttribute("autoWebLoad", autoWebLoad);
        model.addAttribute("htmlCenterShow", htmlCenterShow);
        return "sql/query";
    }


    @Sign(code = "sql:custom")
    @RequestMapping(value = "/custom", method = {RequestMethod.GET, RequestMethod.POST})
    public String custom(Model model, @RequestParam(value = "datasource", defaultValue = "") String datasource, @RequestParam(value = "sql", defaultValue = "") String sql) {
        List<SqlDataSource> dataSourceList = sqlConfigInterface.queryDataSource();
        List<String> head = Lists.newArrayList();
        List<Object> mapList = Lists.newArrayList();
        if (StringUtils.isNotBlank(sql)) {
            try {
                ExecuteResult executeResult = jdbcTemplateService.queryArray2ExecuteResult(datasource, sql, Maps.newHashMap(), Maps.newHashMap());
                head = executeResult.getColumnNames();
                assemblyMapList(mapList, executeResult.getArray(), head);
            } catch (Throwable e) {
                log.error("", e);
                model.addAttribute("messageType", "error");
                model.addAttribute("message", String.join("<BR>", ExceptionUtils.getRootCauseStackTrace(e)));
            }
        }
        model.addAttribute("sql", sql);
        model.addAttribute("datasource", datasource);
        model.addAttribute("dataSourceList", dataSourceList);
        model.addAttribute("thead", head);
        model.addAttribute("theadSplit", String.join(",", head));
        model.addAttribute("tbody", mapList);
        return "sql/custom";
    }


    @Sign
    @SuppressWarnings("all")
    @RequestMapping(value = "/customExport", method = RequestMethod.GET)
    public ResponseEntity<?> customExport(@RequestParam(value = "datasource", defaultValue = "") String datasource, @RequestParam("sql") String sql) {
        try {
            ExecuteResult executeResult = jdbcTemplateService.queryArray2ExecuteResult(datasource, sql, Maps.newHashMap(), Maps.newHashMap());
            List<Map<String, String>> mapList = Lists.newArrayList();
            List<String> head = executeResult.getColumnNames();
            assemblyMapList(mapList, executeResult.getArray(), head);
            HttpHeaders headers = new HttpHeaders();
            HttpStatus httpStatus = HttpStatus.OK;
            String uuid = UUID.randomUUID().toString().replace("-", "");
            File file = File.createTempFile(String.format("%s-%s", uuid, System.currentTimeMillis()), "xlsx");
            headers.add("Content-Disposition", String.format("attachment;filename=%s-%s.xlsx", uuid, System.currentTimeMillis()));
            headers.setContentType(mediaType);
            List<List<String>> excelHead = head.stream().map(Lists::newArrayList).collect(Collectors.toList());
            List<List<String>> excelData = mapList.stream().map(one -> head.stream().map(one::get).map(String::valueOf)
                    .collect(Collectors.toList())).collect(Collectors.toList());
            EasyExcel.write(file)
                    .useDefaultStyle(false)
                    .head(excelHead).sheet("模板")
                    .doWrite(excelData);

            return new ResponseEntity<>(Files.toByteArray(file), headers, httpStatus);
        } catch (Exception e) {
            log.error("", e);
            return new ResponseEntity<>(ExceptionUtils.getStackTrace(e).replace("\n", "<BR>"), HttpStatus.BAD_REQUEST);
        }
    }


    @SuppressWarnings("all")
    private void assemblyMapList(List mapList, JsonArray array, List<String> head) {
        for (int i = 0; i < array.size(); i++) {
            JsonObject object = array.get(i).getAsJsonObject();
            for (String key : head) {
                if (!object.has(key)) {
                    object.addProperty(key, "");
                }
            }
            Map<String, String> map = Maps.newHashMap();
            for (String key : object.keySet()) {
                JsonElement element = object.get(key);
                if (element.isJsonNull()) {
                    map.put(key, "");
                } else if (element.isJsonObject()) {
                    map.put(key, element.getAsJsonObject().toString());
                } else if (element.isJsonArray()) {
                    map.put(key, element.getAsJsonArray().toString());
                } else if (element.isJsonPrimitive()) {
                    map.put(key, element.getAsString());
                }
            }
            map.put("json", Base64.getEncoder().encodeToString(object.toString().getBytes(StandardCharsets.UTF_8)));
            mapList.add(map);
        }
    }

    private Map<String, Object> getParams(HttpServletRequest request) {
        Map<String, Object> params = Maps.newHashMap();
        Enumeration<String> parameterNames = request.getParameterNames();
        while (parameterNames.hasMoreElements()) {
            String name = parameterNames.nextElement();
            final String parameter = request.getParameter(name);
            if (name.startsWith("sql.") && StringUtils.isNotBlank(parameter)) {
                params.put(name.substring(4), parameter.trim());
            }
        }
        return params;
    }

    private void assemblySqlParam(List<SqlDefineParam> sqlDefineParams, Map<String, Object> params, boolean select) {
        Map<String, SqlDefineParam> sqlDefineParamMap = sqlDefineParams.stream().peek(s -> {
            try {
                //下拉列表
                s.assemblyDropDownMap();
                //初始化默认值
                if ((StringUtils.isNotBlank(s.getDefaultValue()))) {
                    s.setDefaultValue(templateRenderService.renderTemplate(s.getDefaultValue()));
                }
            } catch (Exception e) {
                log.warn("", e);
            }
        }).collect(Collectors.toMap(SqlDefineParam::getParamName, Function.identity()));

        if (select) {
            //参数中没包含，非必要参数，设置空值
            sqlDefineParamMap.forEach((k, v) -> {
                if (!params.containsKey(k) && !v.getRequired() && StringUtils.isNotBlank(v.getDefaultValue())) {
                    v.setDefaultValue("");
                }
            });
        }
    }

    private String getExceptionMessage(Exception e) {
        if (e instanceof ParamVerifyException) {
            ParamVerifyException paramVerifyException = (ParamVerifyException) e;
            log.warn("{}", e.getMessage());
            return "参数校验失败->" + paramVerifyException.getMessage().replace("\n", "<BR>");
        } else if (e instanceof MissingServletRequestParameterException) {
            MissingServletRequestParameterException parameterException = (MissingServletRequestParameterException) e;
            log.warn(String.format("参数[%s]缺失，参数类型是[%s]", parameterException.getParameterName(), parameterException.getParameterType()));
            return String.format("参数[%s]缺失，参数类型是[%s]", parameterException.getParameterName(), parameterException.getParameterType());
        } else if (e instanceof MoreTransformException) {
            MoreTransformException moreTransformException = (MoreTransformException) e;
            if (e.getCause() != null) {
                log.error("未知异常 {}", String.join("\n", ExceptionUtils.getRootCauseStackTrace(e)));
            } else {
                return "多转换器转换失败-> " + moreTransformException.getMessage().replace("\n", "<BR>");
            }
        }
        return String.join("<BR>", ExceptionUtils.getRootCauseStackTrace(e));
    }
}
